<?php
//
// interact with database
//
class Plant_model extends CI_Model {

	// plant table
	public $m_dbPlantTable 	= "plant";
	public $m_dbPlantPID 	= "pid";
	public $m_dbPlantName	= "name";

	// year table
	public $m_dbYearTable	= "year";
	public $m_dbYear 		= "year";
	public $m_dbYearPID 	= "pid";
	public $m_dbYearYID 	= "yid";
	
	// flow table
	public $m_dbFlowTable 	= "flow";
	public $m_dbFlowDate	= "date";
	public $m_dbFlowFID		= "fid";
	public $m_dbFlowYID		= "yid";
	public $m_dbFlowIn		= "flow_in";	
	public $m_dbFlowOut		= "flow_out";	

	// quality table
	public $m_dbQtyTable	= "quality";
	public $m_dbQtyQID 		= "qid";
	public $m_dbQtyYID 		= "yid";
	public $m_dbQtyDate 	= "date";

	public $m_dbQty_bod  	 		= "bod";
	public $m_dbQty_bod_out  		= "bod_out";
	public $m_dbQty_cod  	 		= "cod";
	public $m_dbQty_cod_output 		= "cod_output";
	public $m_dbQty_do  			= "do";
	public $m_dbQty_do_out  		= "do_out";
	public $m_dbQty_ph  			= "ph";
	public $m_dbQty_ph_out  		= "ph_out";
	public $m_dbQty_tds  			= "tds";
	public $m_dbQty_tds_out  		= "tds_out";
	public $m_dbQty_ss  			= "ss";
	public $m_dbQty_ss_out  		= "ss_out";
	public $m_dbQty_tkn  			= "tkn";
	public $m_dbQty_tkn_out  		= "tkn_out";
	public $m_dbQty_grease  		= "grease";
	public $m_dbQty_grease_out  	= "grease_out";
	public $m_dbQty_cd  			= "cd";
	public $m_dbQty_cd_out  		= "cd_out";
	public $m_dbQty_cr  			= "cr";
	public $m_dbQty_cr_out  		= "cr_out";
	public $m_dbQty_ni 				= "ni";
	public $m_dbQty_ni_out  		= "ni_out";
	public $m_dbQty_cu 				= "cu";
	public $m_dbQty_cu_out  		= "cu_out";
	public $m_dbQty_zn  			= "zn";
	public $m_dbQty_zn_out  		= "zn_out";
	public $m_dbQty_pb  			= "pb";
	public $m_dbQty_pb_out  		= "pb_out";
	public $m_dbQty_salinity  		= "salinity";
	public $m_dbQty_salinity_out  	= "salinity_out";
	public $m_dbQty_turbidity  		= "turbidity";
	public $m_dbQty_turbidity_out 	= "turbidity_out";
	public $m_dbQty_color  			= "color";
	public $m_dbQty_color_out  		= "color_out";
	public $m_dbQty_tc  			= "tc";
	public $m_dbQty_tc_out  		= "tc_out";
	public $m_dbQty_fc  			= "fc";
	public $m_dbQty_fc_out  		= "fc_out";
	public $m_dbQty_coliphage  		= "coliphage";
	public $m_dbQty_coliphage_out 	= "coliphage_out";
	public $m_dbQty_helminth  		= "helminth";
	public $m_dbQty_helminth_out  	= "helminth_out";
	public $m_dbQty_residual  		= "residual";
	public $m_dbQty_residual_out  	= "residual_out";

	public function __construct()
	{
		parent::__construct();

	}

	public function db_get_plantlist()
	{
		$q = $this
				->db 
				->get($this->m_dbPlantTable);

		// echo "<pre>";
		// print_r($q->result()) ;
		// echo "</pre>";
		//echo $this->dbutil->csv_from_result($q, ",", "<br/>"); die(); //TAN:TEST
				
		return $q->result();
	}
	
	public function db_get_yearlist_from_plant($a_pid)
	{
		$q = $this
				->db 
				->where($this->m_dbYearPID, $a_pid)
				->order_by($this->m_dbYear, "desc")
				->get($this->m_dbYearTable);

		// echo "<pre>";
		// print_r($q->result()) ;
		// echo "</pre>";
		return $q->result();
	}
	
	public function db_get_plantname_from_pid($a_pid)
	{
		$q = $this
				->db 
				->where($this->m_dbPlantPID, $a_pid)
				->get($this->m_dbPlantTable);

		// echo "<pre>";
		// print_r($q->row()) ;
		// echo "</pre>";
		return $q->row()->name;
	}
	public function db_get_row_from_pid($a_pid)
	{
		$q = $this
				->db 
				->where($this->m_dbPlantPID, $a_pid)
				->limit(1)
				->get($this->m_dbPlantTable);
		
		if ($q->num_rows() > 0 ){
			// echo __FUNCTION__;
			// echo "<pre>";
			// print_r($q->row()) ;
			// echo "</pre>";
			return $q->row();
		}
		return FALSE;

	}

	public function db_get_lastyear_from_pid($a_pid)
	{
		$q = $this
			->db 
			->where($this->m_dbYearPID, $a_pid)
			->limit(1)
			->order_by($this->m_dbYear, "desc")
			->get($this->m_dbYearTable);
		
		if ($q->num_rows() > 0 ){
			// echo __FUNCTION__;
			// echo "<pre>";
			// print_r($q->row()) ;
			// echo "</pre>";
			return $q->row();
		}
		return FALSE;
	}
	
	public function db_get_row_from_plantname($a_name)
	{
		$q = $this
				->db 
				->where($this->m_dbPlantName, $a_name)
				->limit(1)
				->get($this->m_dbPlantTable);
		
		if ($q->num_rows() > 0 ){
			// echo "<pre>";
			// print_r($q->row()) ;
			// echo "</pre>";
			return $q->row();
		}
		return FALSE;
	}
	public function db_get_yearname_from_yid($a_yid)
	{
		$q = $this
				->db 
				->where($this->m_dbYearYID, $a_yid)
				->get($this->m_dbYearTable);

		// echo "<pre>";
		// print_r($q->row()) ;
		// echo "</pre>";
		// exit();
		return $q->row()->year;
	}

	public function db_get_row_from_year($a_pid, $a_year)
	{
		$q = $this
				->db 
				->where($this->m_dbYear, $a_year)
				->where($this->m_dbYearPID, $a_pid)
				->limit(1)
				->get($this->m_dbYearTable);
		
		if ($q->num_rows() > 0 ){
			// echo "<pre>";
			// print_r($q->row()) ;
			// echo "</pre>";
			return $q->row();
		}
		return FALSE;
	}

	public function db_get_date_from_yid($a_yid, $a_date)
	{
		date_default_timezone_set("Asia/Bangkok");
		$time = strtotime($a_date);
  		$monthLater = strtotime('+1 month -1 day', $time);
  		$monthLaterString = date('Y-m-d', $monthLater);

		$q = $this
				->db 
				->where($this->m_dbFlowYID, $a_yid)
				->where($this->m_dbFlowDate." >=", $a_date)
				->where($this->m_dbFlowDate." <=", $monthLaterString)
				->limit(32)
				->get($this->m_dbFlowTable);
		
		if ($q->num_rows() > 0 ){
			 // echo "<pre>";
			 // print_r($q->result()) ;
			 // echo $monthLaterString;
			 // echo "</pre>";
			 // exit();
			return $q->result();
		}
		return FALSE;
	}

	public function db_get_3rows_from_yearname($a_yearname)
	{

		$res = $this->db_get_row_from_plantname($a_yearname);
		if ($res != FALSE ){
			// echo "db_get_3rows_from_yearname";
			// echo "<pre>";
			// echo $res->pid;
			// print_r($res);
			// echo "</pre>";

			$res_2 = $this->db_get_lastyear_from_pid($res->pid);
			if ($res_2 != FALSE ){
				date_default_timezone_set("Asia/Bangkok");

				$thaiyear = intval($res_2->year);
				$year = $thaiyear - 543;
				$date = $year."-".date("m")."-01";
				$startDate = date ('Y-m-d', strtotime('-2 month', strtotime( $date )));
				//echo $startDate;
				$rs = $this->db_get_3rows_from_yid($res_2->yid, $startDate);
				if ($rs != FALSE)
				{
					// echo "<pre>";
					// print_r($rs);
					// echo "</pre>"; 
					return $rs;
				}
			}
		}
		return FALSE;
	}
	public function db_get_3rows_from_yid($a_yid, $a_date)
	{
		$joinString = $this->m_dbYearTable.".".$this->m_dbYearYID."=".$this->m_dbQtyTable.".".$this->m_dbQtyYID;
		$q = $this
				->db
				->select('*')
				->from($this->m_dbQtyTable)
				->join($this->m_dbYearTable, $joinString, 'inner')
				->where($this->m_dbQtyDate." >=", $a_date)
				->order_by($this->m_dbQtyDate, "asc")
				->limit(3)
				->get();
		
		if ($q->num_rows() > 0 ){
			// echo "<pre>";
			// print_r($q->result());
			// echo "</pre>";
			return $q->result();
		}
		return FALSE;
	}

	public function db_get_row_from_yid($a_yid, $a_date)
	{
		$q = $this
				->db 
				->where($this->m_dbQtyYID, $a_yid)
				->where($this->m_dbQtyDate, $a_date)
				->limit(1)
				->get($this->m_dbQtyTable);
		
		if ($q->num_rows() > 0 ){
			// echo "<pre>";
			// print_r($q->row()) ;
			// echo "</pre>";
			return $q->row();
		}
		return FALSE;
	}

	public function db_add_new_plant_by_id($a_name)
	{
		// try to find unique ID
		$res = $this->db_get_row_from_plantname($a_name);
		if ($res == FALSE)
		{
			$data = array(
					$this->m_dbPlantName => $a_name
	            );

			$this->db->insert($this->m_dbPlantTable, $data);	
			return TRUE;
		}
		return FALSE;
	}
	public function db_add_new_year_by_id($a_pid, $a_year)
	{
		// try to find unique ID
		$res = $this->db_get_row_from_year($a_pid, $a_year);
		if ($res == FALSE)
		{
			$data = array(
					$this->m_dbYearPID 	=> $a_pid,
					$this->m_dbYear 	=> $a_year
	            );

			$this->db->insert($this->m_dbYearTable, $data);	

			// insert quality
			$this->db_insert_quality_monthly($a_pid, $a_year);

			// insert flow
			$this->db_insert_flow_daily($a_pid, $a_year);
			return TRUE;
		}
		return FALSE;
	}

	public function db_insert_flow_daily($a_pid, $a_year)
	{
		$ret = $this->db_get_row_from_year($a_pid, $a_year);
		if ($ret != FALSE)
		{
			// convert from Thai year to Eng.
			$year = $a_year - 543;

			date_default_timezone_set("Asia/Bangkok");

			$now = mktime(0,0,0,1,1,$year);
			$aYearLater = strtotime('+1 Year', $now);

			$allDates = array();
			$start = $now;

			while(1){
		    	if($start >= $aYearLater)
		    		break 1;
		    	
		    	$myArr = array(
		    		"date"		=> date('Y-m-d', $start),
		    		"yid"		=> $ret->yid
		    	);
		    	$allDates[] = $myArr;

		    	$start = strtotime('+1 day', $start);
		  	}
			$this->db->insert_batch($this->m_dbFlowTable, $allDates);
			return TRUE;
		}
		return FALSE;
	}

	public function db_insert_quality_monthly($a_pid, $a_year)
	{
		$ret = $this->db_get_row_from_year($a_pid, $a_year);
		if ($ret != FALSE)
		{
			$thaiyear = intval($a_year);
   		 	$year = $thaiyear - 543;
			
			$data = array(
				array(  $this->m_dbQtyYID 	=> $ret->yid,
						$this->m_dbQtyDate 	=> $year."-01-01"),
		        array( 	$this->m_dbQtyYID 	=> $ret->yid,
						$this->m_dbQtyDate 	=> $year."-02-01"),
		        array( 	$this->m_dbQtyYID 	=> $ret->yid,
						$this->m_dbQtyDate 	=> $year."-03-01"),
		        array( 	$this->m_dbQtyYID 	=> $ret->yid,
						$this->m_dbQtyDate 	=> $year."-04-01"),
		        array( 	$this->m_dbQtyYID 	=> $ret->yid,
						$this->m_dbQtyDate 	=> $year."-05-01"),
		        array( 	$this->m_dbQtyYID 	=> $ret->yid,
						$this->m_dbQtyDate 	=> $year."-06-01"),
		        array( 	$this->m_dbQtyYID 	=> $ret->yid,
						$this->m_dbQtyDate 	=> $year."-07-01"),
		        array( 	$this->m_dbQtyYID 	=> $ret->yid,
						$this->m_dbQtyDate 	=> $year."-08-01"),
		        array( 	$this->m_dbQtyYID 	=> $ret->yid,
						$this->m_dbQtyDate 	=> $year."-09-01"),
		        array( 	$this->m_dbQtyYID 	=> $ret->yid,
						$this->m_dbQtyDate 	=> $year."-10-01"),
		        array( 	$this->m_dbQtyYID 	=> $ret->yid,
						$this->m_dbQtyDate 	=> $year."-11-01"),
		        array( 	$this->m_dbQtyYID 	=> $ret->yid,
						$this->m_dbQtyDate 	=> $year."-12-01")
			);

			$this->db->insert_batch($this->m_dbQtyTable, $data);
			return TRUE;
		}
		return FALSE;
	}

	public function db_delete_plant_by_id($a_pid)
	{
		//echo "remove plant completed";
		$q = $this
				->db
				->where($this->m_dbPlantPID, $a_pid);
		$q = $this
				->db
				->delete($this->m_dbPlantTable);
		
		// delete all quality related to plantID
		$res = $this->db_get_yearlist_from_plant($a_pid);
		if ($res != FALSE)
		{
			// echo "<pre>";
			// print_r($res);
			// echo "</pre>";
			foreach ($res as $row) {
				$this->db_delete_year_by_yid($row->yid);
			}
		}
		return TRUE;
	}

	public function db_delete_year_by_yid($a_yid)
	{
		$q = $this
				->db
				->where($this->m_dbYearYID, $a_yid);
		$q = $this
				->db
				->delete($this->m_dbYearTable);

		$this->db_delete_quality_by_yid($a_yid);
		
		$this->db_delete_flow_by_yid($a_yid);

		return TRUE;
	}

	public function db_delete_quality_by_yid($a_yid)
	{
		$q = $this
				->db
				->where($this->m_dbQtyYID, $a_yid);
		$q = $this
				->db
				->delete($this->m_dbQtyTable);

		return TRUE;		
	}

	public function db_delete_flow_by_yid($a_yid)
	{
		$q = $this
				->db
				->where($this->m_dbFlowYID, $a_yid);
		$q = $this
				->db
				->delete($this->m_dbFlowTable);

		return TRUE;		
	}
	public function db_rename_plant($a_id, $a_plant)
	{
		$isOK = TRUE;
		$res = $this->db_get_row_from_pid($a_id);
		if ($res != FALSE)
		{
			// check if new name has been inserted.
			if ($res->name != $a_plant)
			{
				//check if new name is existing
				$res_2 = $this->db_get_row_from_plantname($a_plant);
				if ($res_2 != FALSE)
				{
					$isOK = FALSE;
					return FALSE;
				}
			}
		}
		
		if ($isOK)
		{
			$data = array(
				$this->m_dbPlantName => $a_plant
            );
		
			$this->db->where($this->m_dbPlantPID, $a_id);

			$this->db->update($this->m_dbPlantTable, $data);
			return TRUE;
		}
	}

	public function db_update_quality($inputArray)
	{
	
		$this->db->where($this->m_dbQtyYID, $inputArray["yid"]);
		$this->db->where($this->m_dbQtyDate, $inputArray["date"]);

		$this->db->update($this->m_dbQtyTable, $inputArray);
		return TRUE;
	}

	public function db_update_flow($inputArray)
	{
		foreach ($inputArray as $rec)
		{
			$this->db->where($this->m_dbFlowDate, $rec["date"]);
			$this->db->where($this->m_dbFlowYID, $rec["yid"]);

			$this->db->update($this->m_dbFlowTable, $rec);
		}
		// echo "<pre>";
		// print_r($inputArray[0]);
		// echo "</pre>";
		return TRUE;
	}

}
?>
